Predictive RFM: Charles Book Club

Prediction with RFM

The Charles Book Club case was derived, with the assistance of Ms. Vinni Bhandari, from The Bookbinders Club, a Case Study in Database Marketing, prepared by Nissan Levin and Jacob Zahavi, Tel Aviv University

The Charles Book Club (CBC) was established in December 1986 on the premise that a book club could differentiate itself through a deep understanding of its customer base and by delivering uniquely tailored offerings. CBC focused on selling specialty books by direct marketing through a variety of channels, including media advertising (TV, magazines, newspapers) and mailing. CBC is strictly a distributor and does not publish any of the books that it sells. In line with its commitment to understanding its customer base, CBC built and maintained a detailed database about its club members. Upon enrollment, readers were required to fill out an insert and mail it to CBC. Through this process, CBC created an active database of 500,000 readers; most were acquired through advertising in specialty magazines.

Historically, book clubs offered their readers different types of membership programs. Two common membership programs are the continuity and negative option programs, which are both extended contractual relationships between the club and its members. Under a continuity program, a reader signs up by accepting an offer of several books for just a few dollars (plus shipping and handling) and an agreement to receive a shipment of one or two books each month thereafter at more-standard pricing. The continuity program is most common in the children’s book market, where parents are willing to delegate the rights to the book club to make a selection, and much of the club’s prestige depends on the quality of its selections.

In a negative option program, readers get to select how many and which additional books they would like to receive. However, the club’s selection of the month is delivered to them automatically unless they specifically mark “no” on their order form by a deadline date. Negative option programs sometimes result in customer dissatisfaction and always give rise to significant mailing and processing costs.

In an attempt to combat these trends, some book clubs have begun to offer books on a positive option basis, but only to specific segments of their customer base that are likely to be receptive to specific offers. Rather than expanding the volume and coverage of mailings, some book clubs are beginning to use database-marketing techniques to target customers more accurately. Information contained in their databases is used to identify who is most likely to be interested in a specific offer. This information enables clubs to design special programs carefully tailored to meet their customer segments’ varying needs.

The Problem

CBC sent mailings to its club members each month containing the latest offerings. On the surface, CBC appeared very successful: mailing volume was increasing, book selection was diversifying and growing,and their customer database was increasing. However, their bottom-line profits were falling. The decreasing profits led CBC to revisit their original plan of using database marketing to improve mailing yields and to stay profitable.

A Possible Solution

CBC embraced the idea of deriving intelligence from their data to allow them to know their customers better and enable multiple targeted campaigns where each target audience would receive appropriate mailings. CBC’s management decided to focus its efforts on the most profitable customers and prospects, and to design targeted marketing strategies to best reach them. The two processes they had in place were:

1. Customer acquisition:

• New members would be acquired by advertising in specialty magazines, newspapers, and on TV.

• Direct mailing and telemarketing would contact existing club members.

• Every new book would be offered to club members before general advertising.

2. Data collection:

• All customer responses would be recorded and maintained in the database.
• Any information not being collected that is critical would be requested from the customer.

For each new title, they decided to use a two-step approach:

1. Conduct a market test involving a random sample of 4000 customers from the database to enable analysis of customer responses. The analysis would create and calibrate response models for the current book offering.

2. Based on the response models, compute a score for each customer in the database. Use this score and a cutoff value to extract a target customer list for direct-mail promotion.

Targeting promotions was considered to be of prime importance. Other opportunities to create successful marketing campaigns based on customer behavior data (returns, inactivity, complaints, compliments, etc.) would be addressed by CBC at a later stage.

Background Information

A new title, The Art History of Florence, is ready for release. CBC sent a test mailing to a random sample of 4000 customers from its customer base. The customer responses have been collated with past purchase data. The dataset was randomly partitioned into three parts:

• Training Data (1800 customers): initial data to be used to fit models,
• Validation Data (1400 customers): holdout data used to compare the performance of different models,
• and Test Data (800 customers): data to be used only after a final model has been selected to estimate the probable performance of the model when it is deployed.

Each row (or case) in the spreadsheet (other than the header) corresponds to one market test customer. Each column is a variable, with the header row giving the name of the variable.

Recency - Frequency - Monetary Analysis

The segmentation process in database marketing aims to partition customers in a list of prospects into homogeneous groups (segments) that are similar with respect to buying behavior. The homogeneity criterion we need for segmentation is the propensity to purchase the offering. However, since we cannot measure this attribute, we use variables that are plausible indicators of this propensity.

In the direct marketing business, the most commonly used variables are the RFM variables:

• R = recency, time since last purchase
• F = frequency, number of previous purchases from the company over a period
• M = monetary, amount of money spent on the company’s products over a period

The assumption is that the more recent the last purchase, the more products bought from the company in the past, and the more money spent in the past buying the company’s products, the more likely the customer is to purchase the product offered.

The 1800 observations in the dataset were divided into recency, frequency, and monetary categories as follows:

• Recency:

Recency	Recode
0–2 months	Rcode = 1
3–6 months	Rcode = 2
7–12 months	Rcode = 3
13 months and up	Rcode = 4

• Frequency:

Frequency	Recode
1 book	Fcode = 1
2 books	Fcode = 2
3 books	Fcode = 3

• Monetary:

Monetary	Recode
0 – 25	Mcode = 1
26–50	Mcode = 2
51–100	Mcode = 3
101–200	Mcode = 4
201 and up	Mcode = 5
Note: Montary values are denoted in USD

Assignment

Partition the data into training (60%) and validation (40%). Use seed = 1.

What is the response rate for the training data customers taken as a whole? What is the response rate for each of the 4×5×3 = 60 combinations of RFM categories? Which combinations have response rates in the training data that are above the overall response in the training data?

Before we answer the question, let us explore the data set:

Rows: 4,000
Columns: 24
$ Seq.             <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, ...
$ ID.              <int> 25, 29, 46, 47, 51, 60, 61, 79, 81, 90, 95, 100...
$ Gender           <int> 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 0, 1, 1,...
$ M                <int> 297, 128, 138, 228, 257, 145, 190, 187, 252, 24...
$ R                <int> 14, 8, 22, 2, 10, 6, 16, 14, 10, 6, 2, 2, 4, 14...
$ F                <int> 2, 2, 7, 1, 1, 2, 1, 1, 1, 3, 4, 3, 1, 1, 2, 9,...
$ FirstPurch       <int> 22, 10, 56, 2, 10, 12, 16, 14, 10, 20, 20, 18, ...
$ ChildBks         <int> 0, 0, 2, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 2,...
$ YouthBks         <int> 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,...
$ CookBks          <int> 1, 0, 2, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 3,...
$ DoItYBks         <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,...
$ RefBks           <int> 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,...
$ ArtBks           <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1,...
$ GeogBks          <int> 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 2,...
$ ItalCook         <int> 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,...
$ ItalAtlas        <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,...
$ ItalArt          <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,...
$ Florence         <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0,...
$ Related.Purchase <int> 0, 0, 2, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 6,...
$ Mcode            <int> 5, 4, 4, 5, 5, 4, 4, 4, 5, 5, 5, 5, 5, 4, 5, 5,...
$ Rcode            <int> 4, 3, 4, 1, 3, 2, 4, 4, 3, 2, 1, 1, 2, 4, 2, 4,...
$ Fcode            <int> 2, 2, 3, 1, 1, 2, 1, 1, 1, 3, 3, 3, 1, 1, 2, 3,...
$ Yes_Florence     <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0,...
$ No_Florence      <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 1, 1,...

First, we will convert the response variable to factor class.

data$Yes_Florence <- factor(data$Yes_Florence, labels = c("No","Yes"),levels = c(0:1))
data$No_Florence <-factor(data$No_Florence, labels = c("No","Yes"),levels = c(0:1))

Second, we would need to compute RFM score by merging three scores into one cell.

# Calculating RFM score
data$RFM_score<- paste(data$Rcode,data$Fcode,data$Mcode)
data$RFM_score <- gsub(" ","",data$RFM_score)
data$RFM_score<-as.factor(data$RFM_score)
data[1:10,"RFM_score"]

 [1] 425 324 434 115 315 224 414 414 315 235
51 Levels: 111 112 113 114 115 122 123 124 125 132 133 134 135 211 ... 435

Now we will proceed with data partition. For that we will use sample() function, where we indicate rownames and the number of randomly selected row numbers we want to separate from the remaining data set. In our case, out of 4000 rows (Customers), we will randomly assign 2800 to train data set, and the rest to test data.

# Train data
set.seed(1)
trainIndex <- caret::createDataPartition(data$Florence, p = .7, 
                                  list = FALSE, 
                                  times = 1)
train_data <- data[ trainIndex,]

The remaining part of the data set will be assigned to the validation data set.

# Validation set
validation_data  <- data[-trainIndex,]

Visaul exploration

It would be beneficial to inspect relationship between recency, frequency and monetary value in the whole data set before we continue. A quite convenient way to do it is a heatmap. There we can plot all three variables at the same time and inspect the monetary value (= amount spend in the time frame observed) of each customer based on his/her frequency and recency.

Unsuprisingly, customers who purchased more frequently generated more revenue compared to those who visited less frequently. However, customers who spent the most are not the most recent ones (= last purchase in 0-2): the heaviest spenders are the frequent ones, but they haven’t made a purchase 3 to 6 months. Usually, the customers who visited in the recent past (0-2 months) are more likely to return compared to those who made a purchase some time ago as most of those could potentially be lost customers. As such, higher revenue would be associated with most recent visits, but we see that is not really the case here. This could be related to the nature of books as products and the fact that they are not frequently purchased items such as daily products for instance. Nevertheless, it would definitively be worth to consider giving incentives to these customers who spent the most, but the company has not heard of them for 3-6 months.

A legit question for better understanding of our target group would be about the difference in recency of customers who responded to advertising of “Florence”.

data %>%
  rename(Revenue=M,
         Response=Yes_Florence) %>%
  ggplot(aes(x=Response,y=F,fill=Response))+
  geom_boxplot()+
  labs(x="Response to Campaign",y="Frequency (Number of Purchases)",title = "How Frequent Are Customers Interested in 'Florence'?",
       subtitle = "Boxplots depicting frequency of respondents and non-respondents")+
  stat_summary(fun.y=mean, geom="point", shape=20, size=10, color="red", fill="red") +
  theme_bw()

`fun.y` is deprecated. Use `fun` instead.

There seem to be difference in means of the two groups. Non-respondents are somewhat less frequent customers, while respondents belong to more frequent customers. Let us see about their recency.

Customers who responded to the “Florence” campaign have on average lower recency than customers who did not respond. All in all, we can conclude that customers who responded to the campaign are, on average, slightly more frequent and recent than customers who did not respond to campaign.

Response rate in training data

Now we can start with addressing the first question: what is the response rate for training data customers taken as whole?

Let us now inspect the response rate for training data customers.

Response rate of customers from the training data is around 9%.

[1] 0.08571429

Prediction of response rate - single cluster

Now we would need to inspect response rates from all 60 (possible) combinations, and compare them with the overall one.

The following RFM scores indicate response rate higher than 9%:

Suppose that we decide to send promotional mail only to the “above average” RFM combinations identified in part. Now we should compute the response rate in the validation data using these “above average” combinations.

Predicted response rate from the training data set is said to be at around 15.1%, while the true response rate taken from validation set is at 10.3%. From comparison of true response rate in the validation data set and the predicted response rate from the training data set it seems that the latter pretty much deviates from the former. In the next section we will split customers from the training set into 3 clusters, and again compare predicted with the true response rate.

Prediction of response rate - 3 clusters

Let us now segment our customers in 3 different segments:

• Segment 1: RFM combinations that have response rates that exceed twice the overall response rate
• Segment 2: RFM combinations that exceed the overall response rate but do not exceed twice that rate
• Segment 3: the remaining RFM combinations

We classified RFM scores based on response rate into 3 segments. Now we will identify customers with those RFM scores in the validation set, and compare predicted/expected response rate from the training data set with the actual response rate.

By visual inspection we could see that predicted response rates are above the true ones in two out of 3 segments. Customers who had response rate at least twice the initial response rate (9%) were expected to have around 25% response rate, but the true response rate is at significantly lower 9.5% response rate. Similarly, the RFM prediction in case of customers who had response rate between 9 and 18% was slightly inaccurate as well(predicted 14.1 % vs 10.4% true response rate). The only case where true response rate exceeded the predicted response rate is the segment with the response rate below 9%.

However, in order to find out how well our model performed, we will create a gain chart. It helps us determine how effectively we can proceed with our campaign. We aim at selecting a relatively small number of customers and getting a relatively large portion of respondents. For a given number of customers expressed in percentages, the gain curve value on the y-axis will shows us how much better we are doing compared to random choice of customers.

Based on the gain chart, prediction based on our RFM model performs a bit better than baseline, i.e. random guessing. More specifically, if we select top 20% cases based on our model, we would attract 29% of the target customers, i.e. customers who would respond to our marketing campaign.

As the graph is interactive, you are able to check any other percentage you wish. Although we managed to create a model that performs better than just guessing, in the further analyses we will try out other approaches, such as logistic regression, that may provide us better results.

Reference

• Shmueli, G., Bruce, P. C., Yahav, I., Patel, N. R., & Lichtendahl, K. C. (2018). Data mining for business analytics: Concepts, techniques, and applications in R.